﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Text;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using ERP_RORZE_PRODUCT.Product.DAO;
using System.Data.OleDb;
using ERP_RORZE_SYSTEM;
using System.Data.Common;
using System.Data.SqlClient;
using ERP_RORZE_TOOL.Common;
using ERP_RORZE_TOOL.DBAccess;

namespace ERP_RORZE_PRODUCT.Product.LINK_VCUONGDATABASE
{
    public partial class ctrlFormImport : DevExpress.XtraEditors.XtraUserControl
    {
        DataTable dt;
        ClsLinkVCuongDAO clsLVDAO = new ClsLinkVCuongDAO();

        public ctrlFormImport()
        {
            InitializeComponent();
        }
        private void TableList()
        {
            cbTable.Items.Add("Tnhaporder");//0
            cbTable.Items.Add("XTDonhangmuangoai");//1
            cbTable.Items.Add("NhapOrderDonHangChuyenKho");//2
            cbTable.Items.Add("HENKH");//3
            cbTable.Items.Add("KH-NhaplenhtinhthoigianGC");//4
            cbTable.Items.Add("NhapOrderlapKHGC");//5
            cbTable.Items.Add("NhapOrderlapKHNC-Vỉa");//6
            cbTable.Items.Add("NhapOrderlapKHKtra");//7
            cbTable.Items.Add("Check orders");//8

            //Xóa đi, lấy trực tiếp bên phần Tool
            //cbTable.Items.Add("MachineWTS_Temp");//8
            //cbTable.Items.Add("WorkName");//9
            //cbTable.Items.Add("Shift");//10
            //cbTable.Items.Add("Thuy0480");//11
            //cbTable.Items.Add("InternalNGDetails");//12
            //cbTable.Items.Add("MachineList");//13
            //cbTable.Items.Add("MistakeList");//14
            //cbTable.Items.Add("CauseList");//15
            //cbTable.Items.Add("TimeCard");//16
            //cbTable.Items.Add("StaffList");//17
            //cbTable.Items.Add("StaffList1");//18
            //cbTable.Items.Add("TimeCard1");//19
        }

        private void simpleButton1_Click(object sender, EventArgs e)
        {
            if (cbTable.SelectedIndex.ToString() == "0")
            {
                IMport0(sender, e);
            }
            else if (cbTable.SelectedIndex.ToString() == "1")
            {
                IMport1(sender, e);
            }
            else if (cbTable.SelectedIndex.ToString() == "2")
            {
                IMport2(sender, e);
            }
            else if (cbTable.SelectedIndex.ToString() == "3")
            {
                IMport3(sender, e);
            }
            else if (cbTable.SelectedIndex.ToString() == "4")
            {
                IMport4(sender, e);
            }
            else if (cbTable.SelectedIndex.ToString() == "5")
            {
                IMport5(sender, e);
            }
            else if (cbTable.SelectedIndex.ToString() == "6")
            {
                IMport6(sender, e);
            }
            else if (cbTable.SelectedIndex.ToString() == "7")
            {
                IMport7(sender, e);
            }
            else if (cbTable.SelectedIndex.ToString() == "8")
            {
                IMport8();
            }
            else if (cbTable.SelectedIndex.ToString() == "9")
            {
                IMport9(sender, e);
            }
            else if (cbTable.SelectedIndex.ToString() == "10")
            {
                IMport10(sender, e);
            }
            else if (cbTable.SelectedIndex.ToString() == "11")
            {
                IMport11(sender, e);
            }
            else if (cbTable.SelectedIndex.ToString() == "12")
            {
                IMport12(sender, e);
            }
            else if (cbTable.SelectedIndex.ToString() == "13")
            {
                IMport13(sender, e);
            }
            else if (cbTable.SelectedIndex.ToString() == "14")
            {
                IMport14(sender, e);
            }
            else if (cbTable.SelectedIndex.ToString() == "15")
            {
                IMport15(sender, e);
            }
            else if (cbTable.SelectedIndex.ToString() == "16")
            {
                string table = "[rrc_database].[dbo].[242_TimeCard]";
                IMport16(sender, e, table);
            }
            else if (cbTable.SelectedIndex.ToString() == "17")
            {
                string table = "[rrc_database].[dbo].[242_StaffList]";
                IMport17(sender, e, table);
            }
            else if (cbTable.SelectedIndex.ToString() == "18")
            {
                string table = "[rrc_database].[dbo].[242_StaffList1]";
                IMport18(sender, e, table);
            }
            else if (cbTable.SelectedIndex.ToString() == "19")
            {
                string table = "[rrc_database].[dbo].[242_TimeCard1]";
                IMport16(sender, e, table);
            }
        }

        private void ctrlFormImport_Load(object sender, EventArgs e)
        {
            TableList();
        }

        private void cbTable_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cbTable.SelectedIndex.ToString() == "0")
            {
                dt = clsLVDAO.Tnhaporder();
                gridControl1.DataSource = dt;
                gridView1.PopulateColumns();
                gridView1.BestFitColumns();
            }
            else if (cbTable.SelectedIndex.ToString() == "1")
            {
                dt = clsLVDAO.XTDonhangmuangoai();
                gridControl1.DataSource = dt;
                gridView1.PopulateColumns();
                gridView1.BestFitColumns();
            }
            else if (cbTable.SelectedIndex.ToString() == "2")
            {
                dt = clsLVDAO.NhapOrderDonHangChuyenKho();
                gridControl1.DataSource = dt;
                gridView1.PopulateColumns();
                gridView1.BestFitColumns();
            }
            else if (cbTable.SelectedIndex.ToString() == "3")
            {
                dt = clsLVDAO.HENKH();
                gridControl1.DataSource = dt;
                gridView1.PopulateColumns();
                gridView1.BestFitColumns();
            }
            else if (cbTable.SelectedIndex.ToString() == "4")
            {
                dt = clsLVDAO.GetAllGC();
                gridControl1.DataSource = dt;
                gridView1.PopulateColumns();
                gridView1.BestFitColumns();
            }
            else if (cbTable.SelectedIndex.ToString() == "5")
            {
                dt = clsLVDAO.GetAllKHGC();
                gridControl1.DataSource = dt;
                gridView1.PopulateColumns();
                gridView1.BestFitColumns();
            }
            else if (cbTable.SelectedIndex.ToString() == "6")
            {
                dt = clsLVDAO.GetAllKHNCV();
                gridControl1.DataSource = dt;
                gridView1.PopulateColumns();
                gridView1.BestFitColumns();
            }
            else if (cbTable.SelectedIndex.ToString() == "7")
            {
                dt = clsLVDAO.GetAllKHKT();
                gridControl1.DataSource = dt;
                gridView1.PopulateColumns();
                gridView1.BestFitColumns();
            }
            else if (cbTable.SelectedIndex.ToString() == "8")
            {
                dt = BaseDAO.ExecuteQuery("Select * from [242_CheckOrder]");
                gridControl1.DataSource = dt;
                gridView1.PopulateColumns();
                gridView1.BestFitColumns();
            }
            else if (cbTable.SelectedIndex.ToString() == "9")
            {
                dt = clsLVDAO.WorkName();
                gridControl1.DataSource = dt;
                gridView1.PopulateColumns();
                gridView1.BestFitColumns();
            }
            else if (cbTable.SelectedIndex.ToString() == "10")
            {
                dt = clsLVDAO.Shift();
                gridControl1.DataSource = dt;
                gridView1.PopulateColumns();
                gridView1.BestFitColumns();
            }
            else if (cbTable.SelectedIndex.ToString() == "11")
            {
                dt = clsLVDAO.Thuy0480();
                gridControl1.DataSource = dt;
                gridView1.PopulateColumns();
                gridView1.BestFitColumns();
            }
            else if (cbTable.SelectedIndex.ToString() == "12")
            {
                dt = clsLVDAO.InternalNGDetails();
                gridControl1.DataSource = dt;
                gridView1.PopulateColumns();
                gridView1.BestFitColumns();
            }
            else if (cbTable.SelectedIndex.ToString() == "13")
            {
                dt = clsLVDAO.MachineList();
                gridControl1.DataSource = dt;
                gridView1.PopulateColumns();
                gridView1.BestFitColumns();
            }
            else if (cbTable.SelectedIndex.ToString() == "14")
            {
                dt = clsLVDAO.MistakeList();
                gridControl1.DataSource = dt;
                gridView1.PopulateColumns();
                gridView1.BestFitColumns();
            }
            else if (cbTable.SelectedIndex.ToString() == "15")
            {
                dt = clsLVDAO.CauseList();
                gridControl1.DataSource = dt;
                gridView1.PopulateColumns();
                gridView1.BestFitColumns();
            }
            else if (cbTable.SelectedIndex.ToString() == "16")
            {
                dt = clsLVDAO.TimeCard();
                gridControl1.DataSource = dt;
                gridView1.PopulateColumns();
                gridView1.BestFitColumns();
            }
            else if (cbTable.SelectedIndex.ToString() == "17")
            {
                dt = clsLVDAO.StaffList();
                gridControl1.DataSource = dt;
                gridView1.PopulateColumns();
                gridView1.BestFitColumns();
            }
            else if (cbTable.SelectedIndex.ToString() == "18")
            {
                dt = clsLVDAO.StaffList1();
                gridControl1.DataSource = dt;
                gridView1.PopulateColumns();
                gridView1.BestFitColumns();
            }
            else if (cbTable.SelectedIndex.ToString() == "19")
            {
                dt = clsLVDAO.TimeCard1();
                gridControl1.DataSource = dt;
                gridView1.PopulateColumns();
                gridView1.BestFitColumns();
            }
        }
        #region  Import
        #region 0 Tnhaporder

        void IMport0(object sender, EventArgs e)
        {
            FileDialog fileDialog = new OpenFileDialog();
            fileDialog.Filter = "Excel 2007-2013|*.xlsx|Excel 97-2003 (*.xls)|*.xls";
            if (fileDialog.ShowDialog() == DialogResult.OK)
            {
                OleDbConnection connection = new OleDbConnection();
                try
                {
                    string path = fileDialog.FileName;
                    INISettings iniSettings = new INISettings();
                    string connectString = iniSettings.SQLConnectionString;
                    // Connection String to Excel Workbook
                    string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;Persist Security Info=False", path);

                    connection.ConnectionString = excelConnectionString;
                    OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
                    connection.Open();
                    // Create DbDataReader to Data Worksheet
                    DbDataReader dr = command.ExecuteReader();

                    // Bulk Copy to SQL Server 
                    SqlBulkCopy bulkInsert = new SqlBulkCopy(connectString);
                    bulkInsert.DestinationTableName = "[rrc_database].[dbo].[242_Tnhaporder]";

                    SqlBulkCopyColumnMapping date = new SqlBulkCopyColumnMapping("InputDate", "InputDate");
                    bulkInsert.ColumnMappings.Add(date);

                    SqlBulkCopyColumnMapping toolId = new SqlBulkCopyColumnMapping("Order", "Order");
                    bulkInsert.ColumnMappings.Add(toolId);

                    bulkInsert.WriteToServer(dr);

                    connection.Close();
                    MessageBox.Show("Đã Import xong");
                    cbTable_SelectedIndexChanged(sender, e);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    connection.Close();
                }

            }
        }

        #endregion


        #region 1 XTDonhangmuangoai

        void IMport1(object sender, EventArgs e)
        {
            FileDialog fileDialog = new OpenFileDialog();
            fileDialog.Filter = "Excel 2007-2013|*.xlsx|Excel 97-2003 (*.xls)|*.xls";
            if (fileDialog.ShowDialog() == DialogResult.OK)
            {
                OleDbConnection connection = new OleDbConnection();
                try
                {
                    string path = fileDialog.FileName;
                    INISettings iniSettings = new INISettings();
                    string connectString = iniSettings.SQLConnectionString;
                    // Connection String to Excel Workbook
                    string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;Persist Security Info=False", path);

                    connection.ConnectionString = excelConnectionString;
                    OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
                    connection.Open();
                    // Create DbDataReader to Data Worksheet
                    DbDataReader dr = command.ExecuteReader();

                    // Bulk Copy to SQL Server 
                    SqlBulkCopy bulkInsert = new SqlBulkCopy(connectString);
                    bulkInsert.DestinationTableName = "[rrc_database].[dbo].[242_XTDonhangmuangoai]";

                    SqlBulkCopyColumnMapping date = new SqlBulkCopyColumnMapping("Order", "Order");
                    bulkInsert.ColumnMappings.Add(date);

                    SqlBulkCopyColumnMapping toolId = new SqlBulkCopyColumnMapping("PartNo", "PartNo");
                    bulkInsert.ColumnMappings.Add(toolId);

                    SqlBulkCopyColumnMapping Moqty = new SqlBulkCopyColumnMapping("Moqty", "Moqty");
                    bulkInsert.ColumnMappings.Add(Moqty);

                    SqlBulkCopyColumnMapping Deadline = new SqlBulkCopyColumnMapping("Deadline", "Deadline");
                    bulkInsert.ColumnMappings.Add(Deadline);

                    SqlBulkCopyColumnMapping Sup = new SqlBulkCopyColumnMapping("Sup", "Sup");
                    bulkInsert.ColumnMappings.Add(Sup);

                    bulkInsert.WriteToServer(dr);

                    connection.Close();
                    MessageBox.Show("Đã Import xong");
                    cbTable_SelectedIndexChanged(sender, e);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    connection.Close();
                }

            }
        }

        #endregion

        #region 2 NhapOrderDonHangChuyenKho
        void IMport2(object sender, EventArgs e)
        {
            FileDialog fileDialog = new OpenFileDialog();
            fileDialog.Filter = "Excel 2007-2013|*.xlsx|Excel 97-2003 (*.xls)|*.xls";
            if (fileDialog.ShowDialog() == DialogResult.OK)
            {
                OleDbConnection connection = new OleDbConnection();
                try
                {
                    string path = fileDialog.FileName;
                    INISettings iniSettings = new INISettings();
                    string connectString = iniSettings.SQLConnectionString;
                    // Connection String to Excel Workbook
                    string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;Persist Security Info=False", path);

                    connection.ConnectionString = excelConnectionString;
                    OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
                    connection.Open();
                    // Create DbDataReader to Data Worksheet
                    DbDataReader dr = command.ExecuteReader();

                    // Bulk Copy to SQL Server 
                    SqlBulkCopy bulkInsert = new SqlBulkCopy(connectString);
                    bulkInsert.DestinationTableName = "[rrc_database].[dbo].[242_NhapOrderDonHangChuyenKho]";

                    SqlBulkCopyColumnMapping date = new SqlBulkCopyColumnMapping("MONo", "MONo");
                    bulkInsert.ColumnMappings.Add(date);

                    bulkInsert.WriteToServer(dr);

                    connection.Close();
                    MessageBox.Show("Đã Import xong");
                    cbTable_SelectedIndexChanged(sender, e);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    connection.Close();
                }

            }
        }
        #endregion

        #region 3 HENKH
        void IMport3(object sender, EventArgs e)
        {
            FileDialog fileDialog = new OpenFileDialog();
            fileDialog.Filter = "Excel 2007-2013|*.xlsx|Excel 97-2003 (*.xls)|*.xls";
            if (fileDialog.ShowDialog() == DialogResult.OK)
            {
                OleDbConnection connection = new OleDbConnection();
                try
                {
                    string path = fileDialog.FileName;
                    INISettings iniSettings = new INISettings();
                    string connectString = iniSettings.SQLConnectionString;
                    // Connection String to Excel Workbook
                    string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;Persist Security Info=False", path);

                    connection.ConnectionString = excelConnectionString;
                    OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
                    connection.Open();
                    // Create DbDataReader to Data Worksheet
                    DbDataReader dr = command.ExecuteReader();

                    // Bulk Copy to SQL Server 
                    SqlBulkCopy bulkInsert = new SqlBulkCopy(connectString);
                    bulkInsert.DestinationTableName = "[rrc_database].[dbo].[242_HenKH]";

                    SqlBulkCopyColumnMapping date = new SqlBulkCopyColumnMapping("Date", "Date");
                    bulkInsert.ColumnMappings.Add(date);

                    SqlBulkCopyColumnMapping MONumber = new SqlBulkCopyColumnMapping("MONumber", "MONumber");
                    bulkInsert.ColumnMappings.Add(MONumber);

                    SqlBulkCopyColumnMapping In_Plan = new SqlBulkCopyColumnMapping("In_Plan", "In_Plan");
                    bulkInsert.ColumnMappings.Add(In_Plan);

                    SqlBulkCopyColumnMapping Out_Plan = new SqlBulkCopyColumnMapping("Out_Plan", "Out_Plan");
                    bulkInsert.ColumnMappings.Add(Out_Plan);

                    SqlBulkCopyColumnMapping Mitting = new SqlBulkCopyColumnMapping("Mitting", "Mitting");
                    bulkInsert.ColumnMappings.Add(Mitting);

                    SqlBulkCopyColumnMapping Days = new SqlBulkCopyColumnMapping("5Days", "5Days");
                    bulkInsert.ColumnMappings.Add(Days);

                    bulkInsert.WriteToServer(dr);

                    connection.Close();
                    MessageBox.Show("Đã Import xong");
                    cbTable_SelectedIndexChanged(sender, e);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    connection.Close();
                }

            }
        }
        #endregion

        #region 4 KH-NhaplenhtinhthoigianGC
        void IMport4(object sender, EventArgs e)
        {
            FileDialog fileDialog = new OpenFileDialog();
            fileDialog.Filter = "Excel 2007-2013|*.xlsx|Excel 97-2003 (*.xls)|*.xls";
            if (fileDialog.ShowDialog() == DialogResult.OK)
            {
                OleDbConnection connection = new OleDbConnection();
                try
                {
                    string path = fileDialog.FileName;
                    INISettings iniSettings = new INISettings();
                    string connectString = iniSettings.SQLConnectionString;
                    // Connection String to Excel Workbook
                    string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;Persist Security Info=False", path);

                    connection.ConnectionString = excelConnectionString;
                    OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
                    connection.Open();
                    // Create DbDataReader to Data Worksheet
                    DbDataReader dr = command.ExecuteReader();

                    // Bulk Copy to SQL Server 
                    SqlBulkCopy bulkInsert = new SqlBulkCopy(connectString);
                    bulkInsert.DestinationTableName = "[rrc_database].[dbo].[242_KH-NhaplenhtinhthoigianGC]";

                    SqlBulkCopyColumnMapping date = new SqlBulkCopyColumnMapping("MONumber", "MONumber");
                    bulkInsert.ColumnMappings.Add(date);

                    SqlBulkCopyColumnMapping MONumber = new SqlBulkCopyColumnMapping("PartNo", "PartNo");
                    bulkInsert.ColumnMappings.Add(MONumber);

                    SqlBulkCopyColumnMapping In_Plan = new SqlBulkCopyColumnMapping("Deadline", "Deadline");
                    bulkInsert.ColumnMappings.Add(In_Plan);

                    SqlBulkCopyColumnMapping Out_Plan = new SqlBulkCopyColumnMapping("Qty", "Qty");
                    bulkInsert.ColumnMappings.Add(Out_Plan);

                    bulkInsert.WriteToServer(dr);

                    connection.Close();
                    MessageBox.Show("Đã Import xong");
                    cbTable_SelectedIndexChanged(sender, e);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    connection.Close();
                }

            }
        }
        #endregion

        #region 5 NhapOrderlapKHGC
        void IMport5(object sender, EventArgs e)
        {
            FileDialog fileDialog = new OpenFileDialog();
            fileDialog.Filter = "Excel 2007-2013|*.xlsx|Excel 97-2003 (*.xls)|*.xls";
            if (fileDialog.ShowDialog() == DialogResult.OK)
            {
                OleDbConnection connection = new OleDbConnection();
                try
                {
                    string path = fileDialog.FileName;
                    INISettings iniSettings = new INISettings();
                    string connectString = iniSettings.SQLConnectionString;
                    // Connection String to Excel Workbook
                    string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;Persist Security Info=False", path);

                    connection.ConnectionString = excelConnectionString;
                    OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
                    connection.Open();
                    // Create DbDataReader to Data Worksheet
                    DbDataReader dr = command.ExecuteReader();

                    // Bulk Copy to SQL Server 
                    SqlBulkCopy bulkInsert = new SqlBulkCopy(connectString);
                    bulkInsert.DestinationTableName = "[rrc_database].[dbo].[242_P_NhapOrderlapKHGC]";

                    SqlBulkCopyColumnMapping date = new SqlBulkCopyColumnMapping("Date", "Date");
                    bulkInsert.ColumnMappings.Add(date);

                    SqlBulkCopyColumnMapping MONumber = new SqlBulkCopyColumnMapping("MONumber", "MONumber");
                    bulkInsert.ColumnMappings.Add(MONumber);

                    SqlBulkCopyColumnMapping In_Plan = new SqlBulkCopyColumnMapping("Note", "Note");
                    bulkInsert.ColumnMappings.Add(In_Plan);

                    bulkInsert.WriteToServer(dr);

                    connection.Close();
                    MessageBox.Show("Đã Import xong");
                    cbTable_SelectedIndexChanged(sender, e);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    connection.Close();
                }

            }
        }
        #endregion

        #region 6 NhapOrderlapKHNC-Vỉa
        void IMport6(object sender, EventArgs e)
        {
            FileDialog fileDialog = new OpenFileDialog();
            fileDialog.Filter = "Excel 2007-2013|*.xlsx|Excel 97-2003 (*.xls)|*.xls";
            if (fileDialog.ShowDialog() == DialogResult.OK)
            {
                OleDbConnection connection = new OleDbConnection();
                try
                {
                    string path = fileDialog.FileName;
                    INISettings iniSettings = new INISettings();
                    string connectString = iniSettings.SQLConnectionString;
                    // Connection String to Excel Workbook
                    string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;Persist Security Info=False", path);

                    connection.ConnectionString = excelConnectionString;
                    OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
                    connection.Open();
                    // Create DbDataReader to Data Worksheet
                    DbDataReader dr = command.ExecuteReader();

                    // Bulk Copy to SQL Server 
                    SqlBulkCopy bulkInsert = new SqlBulkCopy(connectString);
                    bulkInsert.DestinationTableName = "[rrc_database].[dbo].[242_P_NhapOrderlapKHNCVia]";

                    SqlBulkCopyColumnMapping date = new SqlBulkCopyColumnMapping("Date", "Date");
                    bulkInsert.ColumnMappings.Add(date);

                    SqlBulkCopyColumnMapping MONumber = new SqlBulkCopyColumnMapping("MONumber", "MONumber");
                    bulkInsert.ColumnMappings.Add(MONumber);

                    bulkInsert.WriteToServer(dr);

                    connection.Close();
                    MessageBox.Show("Đã Import xong");
                    cbTable_SelectedIndexChanged(sender, e);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    connection.Close();
                }

            }
        }
        #endregion

        #region 7 NhapOrderlapKHKtra
        void IMport7(object sender, EventArgs e)
        {
            FileDialog fileDialog = new OpenFileDialog();
            fileDialog.Filter = "Excel 2007-2013|*.xlsx|Excel 97-2003 (*.xls)|*.xls";
            if (fileDialog.ShowDialog() == DialogResult.OK)
            {
                OleDbConnection connection = new OleDbConnection();
                try
                {
                    string path = fileDialog.FileName;
                    INISettings iniSettings = new INISettings();
                    string connectString = iniSettings.SQLConnectionString;
                    // Connection String to Excel Workbook
                    string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;Persist Security Info=False", path);

                    connection.ConnectionString = excelConnectionString;
                    OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
                    connection.Open();
                    // Create DbDataReader to Data Worksheet
                    DbDataReader dr = command.ExecuteReader();

                    // Bulk Copy to SQL Server 
                    SqlBulkCopy bulkInsert = new SqlBulkCopy(connectString);
                    bulkInsert.DestinationTableName = "[rrc_database].[dbo].[242_P_NhapOrderlapKHKtra]";

                    SqlBulkCopyColumnMapping date = new SqlBulkCopyColumnMapping("Date", "Date");
                    bulkInsert.ColumnMappings.Add(date);

                    SqlBulkCopyColumnMapping MONumber = new SqlBulkCopyColumnMapping("MONumber", "MONumber");
                    bulkInsert.ColumnMappings.Add(MONumber);

                    bulkInsert.WriteToServer(dr);

                    connection.Close();
                    MessageBox.Show("Đã Import xong");
                    cbTable_SelectedIndexChanged(sender, e);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    connection.Close();
                }

            }
        }
        #endregion

        #region 8 [242_CheckOrder]
        private void IMport8()
        {
            INISettings mySetting = new INISettings();
            SqlConnection myConnection = new SqlConnection(mySetting.SQLConnectionString);

            int countOk = 0;
            int countDup = 0;
            int countErr = 0;

            List<Utilities.ListItem> listDup = new List<Utilities.ListItem>();
            List<Utilities.ListItem> listErr = new List<Utilities.ListItem>();
            FileDialog importFile = new OpenFileDialog();
            importFile.Filter = "Excel 2007-2013|*.xlsx|Excel 97-2003 (*.xls)|*.xls";
            if (importFile.ShowDialog() == DialogResult.OK)
            {
                OleDbConnection excelConnection = null;
                try
                {
                    Control parent = progressPanel1.Parent;
                    progressPanel1.Location =
                        new Point(parent.Bounds.X + parent.Bounds.Width / 2 - progressPanel1.Width / 2,
                                  parent.Bounds.Y + parent.Bounds.Height / 2 - progressPanel1.Height / 2);
                    progressPanel1.Visible = true;
                    string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                                                   importFile.FileName +
                                                   ";Extended Properties=Excel 12.0;Persist Security Info=False";
                    excelConnection = new OleDbConnection(excelConnectionString);
                    OleDbCommand cmd =
                        new OleDbCommand("Select [InputDate],[Order] from [Sheet1$]",
                                         excelConnection);
                    excelConnection.Open();
                    OleDbDataReader dReader;
                    dReader = cmd.ExecuteReader();
                    int i = 0;
                    int line = 1;
                    string lineDup = string.Empty;
                    string lineNo = string.Empty;

                    string InputDate = string.Empty;
                    string Order = string.Empty;

                    myConnection.Open();
                    while (dReader.Read())
                    {
                        bool exist = false;
                        line++;

                        //check null
                        try
                        {
                            if (dReader["InputDate"].ToString().Trim().Equals(string.Empty)
                                && dReader["Order"].ToString().Trim().Equals(string.Empty)
                                )
                            {
                                countErr++;
                                listErr.Add(new Utilities.ListItem(line.ToString(), dReader["InputDate"] + " | " + dReader["Order"], "Null line"));
                                exist = true;
                            }
                        }
                        catch (Exception e)
                        {
                            countErr++;
                            listErr.Add(new Utilities.ListItem(line.ToString(), dReader["InputDate"] + " | " + dReader["Order"], e.Message));
                            exist = true;
                        }

                        //check valid data
                        if (!exist)
                        {
                            try
                            {
                                InputDate = (dReader["InputDate"].ToString().Equals(string.Empty))
                                                ? "null"
                                                : "'" + DateTime.Parse(dReader["InputDate"].ToString()).ToString("yyyy/MM/dd") + "'";

                                Order = (dReader["Order"].ToString().Equals(string.Empty))
                                             ? "null"
                                             : "N'" + dReader["Order"].ToString() + "'";

                            }
                            catch (Exception ex)
                            {
                                exist = true;
                                listErr.Add(new Utilities.ListItem(line.ToString(),
                                                                  dReader["InputDate"] + " | " + dReader["Order"],
                                                                   ex.Message));
                            }
                        }
                        //check dup Order in [242_BusOrder]?
                        if (!exist)
                        {

                            try
                            {
                                SqlCommand cmd_sql = myConnection.CreateCommand();
                                cmd_sql.CommandText = "SELECT * FROM [242_BusOder] WHERE [BOderNo]='" +
                                                      dReader["Order"].ToString() + "'";
                                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                                if (dataReader.Read())
                                {

                                    exist = true;
                                    countErr++;
                                    listDup.Add(new Utilities.ListItem(line.ToString(), dReader["InputDate"] + " | " + dReader["Order"],
                                                                       "Duplicate Order"));
                                    //lineNo += line.ToString() + ", ";
                                }
                                dataReader.Close();
                                cmd_sql.Dispose();
                            }
                            catch (Exception ex)
                            {
                                exist = true;
                                listErr.Add(new Utilities.ListItem(line.ToString(), dReader["InputDate"] + " | " + dReader["Order"],
                                                                   ex.Message));
                            }
                        }

                        //Insert to database
                        if (!exist)
                        {
                            try
                            {
                                string sql = "INSERT INTO [242_CheckOrder] ("
                                         + "[InputDate],[Order] )"
                                         + " VALUES (" + InputDate + ", " + Order + ")";
                                SqlCommand cmd_sql = new SqlCommand(sql, myConnection);

                                cmd_sql.ExecuteNonQuery();
                                cmd_sql.Dispose();
                                countOk++;
                            }
                            catch (Exception ex)
                            {
                                listErr.Add(new Utilities.ListItem(line.ToString(), dReader["InputDate"] + " | " + dReader["Order"], ex.Message));
                            }

                        }
                    }
                    myConnection.Close();
                    progressPanel1.Visible = false;
                    excelConnection.Close();
                    //MessageBox.Show(i + " records have been imported. \n" + countDup.ToString() + " record(s) duplicated (Lines: " + lineDup + ")\nLines have error OptionID: " + lineNo);
                    //LoadData();
                    Frm_ImportReport report = new Frm_ImportReport(countOk, listDup, listErr, "CheckOrders", "InputDate | Order");
                    report.StartPosition = FormStartPosition.CenterScreen;
                    report.Show();
                    report.Activate();
                }
                catch (Exception ex)
                {
                    if (excelConnection.State == ConnectionState.Open)
                    {
                        excelConnection.Close();
                    }
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    progressPanel1.Visible = false;
                    MessageBox.Show(ex.Message);
                }

            }
        }

        #endregion

        #region 9 WorkName
        void IMport9(object sender, EventArgs e)
        {
            FileDialog fileDialog = new OpenFileDialog();
            fileDialog.Filter = "Excel 2007-2013|*.xlsx|Excel 97-2003 (*.xls)|*.xls";
            if (fileDialog.ShowDialog() == DialogResult.OK)
            {
                OleDbConnection connection = new OleDbConnection();
                try
                {
                    string path = fileDialog.FileName;
                    INISettings iniSettings = new INISettings();
                    string connectString = iniSettings.SQLConnectionString;
                    // Connection String to Excel Workbook
                    string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;Persist Security Info=False", path);

                    connection.ConnectionString = excelConnectionString;
                    OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
                    connection.Open();
                    // Create DbDataReader to Data Worksheet
                    DbDataReader dr = command.ExecuteReader();

                    // Bulk Copy to SQL Server 
                    SqlBulkCopy bulkInsert = new SqlBulkCopy(connectString);
                    bulkInsert.DestinationTableName = "[rrc_database].[dbo].[242_WorkName]";

                    SqlBulkCopyColumnMapping date = new SqlBulkCopyColumnMapping("WorkID", "WorkID");
                    bulkInsert.ColumnMappings.Add(date);

                    SqlBulkCopyColumnMapping MONumber = new SqlBulkCopyColumnMapping("Vietnamese", "Vietnamese");
                    bulkInsert.ColumnMappings.Add(MONumber);

                    SqlBulkCopyColumnMapping Japanese = new SqlBulkCopyColumnMapping("Japanese", "Japanese");
                    bulkInsert.ColumnMappings.Add(Japanese);

                    bulkInsert.WriteToServer(dr);

                    connection.Close();
                    MessageBox.Show("Đã Import xong");
                    cbTable_SelectedIndexChanged(sender, e);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    connection.Close();
                }

            }
        }
        #endregion

        #region 10 Shift
        void IMport10(object sender, EventArgs e)
        {
            FileDialog fileDialog = new OpenFileDialog();
            fileDialog.Filter = "Excel 2007-2013|*.xlsx|Excel 97-2003 (*.xls)|*.xls";
            if (fileDialog.ShowDialog() == DialogResult.OK)
            {
                OleDbConnection connection = new OleDbConnection();
                try
                {
                    string path = fileDialog.FileName;
                    INISettings iniSettings = new INISettings();
                    string connectString = iniSettings.SQLConnectionString;
                    // Connection String to Excel Workbook
                    string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;Persist Security Info=False", path);

                    connection.ConnectionString = excelConnectionString;
                    OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
                    connection.Open();
                    // Create DbDataReader to Data Worksheet
                    DbDataReader dr = command.ExecuteReader();

                    // Bulk Copy to SQL Server 
                    SqlBulkCopy bulkInsert = new SqlBulkCopy(connectString);
                    bulkInsert.DestinationTableName = "[rrc_database].[dbo].[242_Shift]";

                    SqlBulkCopyColumnMapping date = new SqlBulkCopyColumnMapping("Shift", "Shift");
                    bulkInsert.ColumnMappings.Add(date);

                    bulkInsert.WriteToServer(dr);

                    connection.Close();
                    MessageBox.Show("Đã Import xong");
                    cbTable_SelectedIndexChanged(sender, e);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    connection.Close();
                }

            }
        }
        #endregion

        #region 11 Thuy0480
        void IMport11(object sender, EventArgs e)
        {
            FileDialog fileDialog = new OpenFileDialog();
            fileDialog.Filter = "Excel 2007-2013|*.xlsx|Excel 97-2003 (*.xls)|*.xls";
            if (fileDialog.ShowDialog() == DialogResult.OK)
            {
                OleDbConnection connection = new OleDbConnection();
                try
                {
                    string path = fileDialog.FileName;
                    INISettings iniSettings = new INISettings();
                    string connectString = iniSettings.SQLConnectionString;
                    // Connection String to Excel Workbook
                    string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;Persist Security Info=False", path);

                    connection.ConnectionString = excelConnectionString;
                    OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
                    connection.Open();
                    // Create DbDataReader to Data Worksheet
                    DbDataReader dr = command.ExecuteReader();

                    // Bulk Copy to SQL Server 
                    SqlBulkCopy bulkInsert = new SqlBulkCopy(connectString);
                    bulkInsert.DestinationTableName = "[rrc_database].[dbo].[242_Thuy0480]";

                    SqlBulkCopyColumnMapping date = new SqlBulkCopyColumnMapping("Date", "Date");
                    bulkInsert.ColumnMappings.Add(date);

                    SqlBulkCopyColumnMapping Month = new SqlBulkCopyColumnMapping("Month", "Month");
                    bulkInsert.ColumnMappings.Add(Month);

                    SqlBulkCopyColumnMapping PartNo = new SqlBulkCopyColumnMapping("PartNo", "PartNo");
                    bulkInsert.ColumnMappings.Add(PartNo);

                    SqlBulkCopyColumnMapping PartName = new SqlBulkCopyColumnMapping("PartName", "PartName");
                    bulkInsert.ColumnMappings.Add(PartName);

                    SqlBulkCopyColumnMapping BusOrder = new SqlBulkCopyColumnMapping("BusOrder", "BusOrder");
                    bulkInsert.ColumnMappings.Add(BusOrder);

                    SqlBulkCopyColumnMapping Quantity = new SqlBulkCopyColumnMapping("Quantity", "Quantity");
                    bulkInsert.ColumnMappings.Add(Quantity);

                    SqlBulkCopyColumnMapping Order = new SqlBulkCopyColumnMapping("Order", "Order");
                    bulkInsert.ColumnMappings.Add(Order);

                    SqlBulkCopyColumnMapping F2Order = new SqlBulkCopyColumnMapping("F2Order", "F2Order");
                    bulkInsert.ColumnMappings.Add(F2Order);

                    SqlBulkCopyColumnMapping OK = new SqlBulkCopyColumnMapping("OK", "OK");
                    bulkInsert.ColumnMappings.Add(OK);

                    SqlBulkCopyColumnMapping NG = new SqlBulkCopyColumnMapping("NG", "NG");
                    bulkInsert.ColumnMappings.Add(NG);

                    SqlBulkCopyColumnMapping UnitPrice = new SqlBulkCopyColumnMapping("UnitPrice", "UnitPrice");
                    bulkInsert.ColumnMappings.Add(UnitPrice);

                    SqlBulkCopyColumnMapping OK_USD = new SqlBulkCopyColumnMapping("OK_USD", "OK_USD");
                    bulkInsert.ColumnMappings.Add(OK_USD);

                    SqlBulkCopyColumnMapping NG_USD = new SqlBulkCopyColumnMapping("NG_USD", "NG_USD");
                    bulkInsert.ColumnMappings.Add(NG_USD);

                    SqlBulkCopyColumnMapping Customer = new SqlBulkCopyColumnMapping("Customer", "Customer");
                    bulkInsert.ColumnMappings.Add(Customer);

                    SqlBulkCopyColumnMapping Material = new SqlBulkCopyColumnMapping("Material", "Material");
                    bulkInsert.ColumnMappings.Add(Material);

                    SqlBulkCopyColumnMapping Month1 = new SqlBulkCopyColumnMapping("Month1", "Month1");
                    bulkInsert.ColumnMappings.Add(Month1);

                    SqlBulkCopyColumnMapping OK1 = new SqlBulkCopyColumnMapping("OK1", "OK1");
                    bulkInsert.ColumnMappings.Add(OK1);

                    SqlBulkCopyColumnMapping NG1 = new SqlBulkCopyColumnMapping("NG1", "NG1");
                    bulkInsert.ColumnMappings.Add(NG1);

                    SqlBulkCopyColumnMapping Ending = new SqlBulkCopyColumnMapping("Ending", "Ending");
                    bulkInsert.ColumnMappings.Add(Ending);

                    SqlBulkCopyColumnMapping PriceDay = new SqlBulkCopyColumnMapping("PriceDay", "PriceDay");
                    bulkInsert.ColumnMappings.Add(PriceDay);

                    SqlBulkCopyColumnMapping Y = new SqlBulkCopyColumnMapping("Y", "Y");
                    bulkInsert.ColumnMappings.Add(Y);

                    bulkInsert.WriteToServer(dr);

                    connection.Close();
                    MessageBox.Show("Đã Import xong");
                    cbTable_SelectedIndexChanged(sender, e);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    connection.Close();
                }

            }
        }
        #endregion

        #region 12 InternalNGDetails
        void IMport12(object sender, EventArgs e)
        {
            FileDialog fileDialog = new OpenFileDialog();
            fileDialog.Filter = "Excel 2007-2013|*.xlsx|Excel 97-2003 (*.xls)|*.xls";
            if (fileDialog.ShowDialog() == DialogResult.OK)
            {
                OleDbConnection connection = new OleDbConnection();
                try
                {
                    string path = fileDialog.FileName;
                    INISettings iniSettings = new INISettings();
                    string connectString = iniSettings.SQLConnectionString;
                    // Connection String to Excel Workbook
                    string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;Persist Security Info=False", path);

                    connection.ConnectionString = excelConnectionString;
                    OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
                    connection.Open();
                    // Create DbDataReader to Data Worksheet
                    DbDataReader dr = command.ExecuteReader();

                    // Bulk Copy to SQL Server 
                    SqlBulkCopy bulkInsert = new SqlBulkCopy(connectString);
                    bulkInsert.DestinationTableName = "[rrc_database].[dbo].[242_InternalNGDetails]";

                    SqlBulkCopyColumnMapping date = new SqlBulkCopyColumnMapping("Date", "Date");
                    bulkInsert.ColumnMappings.Add(date);

                    SqlBulkCopyColumnMapping OrderNo = new SqlBulkCopyColumnMapping("OrderNo", "OrderNo");
                    bulkInsert.ColumnMappings.Add(OrderNo);

                    SqlBulkCopyColumnMapping Quantity = new SqlBulkCopyColumnMapping("Quantity", "Quantity");
                    bulkInsert.ColumnMappings.Add(Quantity);

                    SqlBulkCopyColumnMapping Stage = new SqlBulkCopyColumnMapping("Stage", "Stage");
                    bulkInsert.ColumnMappings.Add(Stage);

                    SqlBulkCopyColumnMapping MachineID = new SqlBulkCopyColumnMapping("MachineID", "MachineID");
                    bulkInsert.ColumnMappings.Add(MachineID);

                    SqlBulkCopyColumnMapping MistakeContent = new SqlBulkCopyColumnMapping("MistakeContent", "MistakeContent");
                    bulkInsert.ColumnMappings.Add(MistakeContent);

                    SqlBulkCopyColumnMapping MistakeID = new SqlBulkCopyColumnMapping("MistakeID", "MistakeID");
                    bulkInsert.ColumnMappings.Add(MistakeID);

                    SqlBulkCopyColumnMapping CauseContent = new SqlBulkCopyColumnMapping("CauseContent", "CauseContent");
                    bulkInsert.ColumnMappings.Add(CauseContent);

                    SqlBulkCopyColumnMapping CauseID = new SqlBulkCopyColumnMapping("CauseID", "CauseID");
                    bulkInsert.ColumnMappings.Add(CauseID);

                    SqlBulkCopyColumnMapping KindID = new SqlBulkCopyColumnMapping("KindID", "KindID");
                    bulkInsert.ColumnMappings.Add(KindID);

                    SqlBulkCopyColumnMapping StaffID = new SqlBulkCopyColumnMapping("StaffID", "StaffID");
                    bulkInsert.ColumnMappings.Add(StaffID);

                    SqlBulkCopyColumnMapping Note = new SqlBulkCopyColumnMapping("Note", "Note");
                    bulkInsert.ColumnMappings.Add(Note);

                    bulkInsert.WriteToServer(dr);

                    connection.Close();
                    MessageBox.Show("Đã Import xong");
                    cbTable_SelectedIndexChanged(sender, e);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    connection.Close();
                }

            }
        }
        #endregion

        #region 13 MachineList
        void IMport13(object sender, EventArgs e)
        {
            FileDialog fileDialog = new OpenFileDialog();
            fileDialog.Filter = "Excel 2007-2013|*.xlsx|Excel 97-2003 (*.xls)|*.xls";
            if (fileDialog.ShowDialog() == DialogResult.OK)
            {
                OleDbConnection connection = new OleDbConnection();
                try
                {
                    string path = fileDialog.FileName;
                    INISettings iniSettings = new INISettings();
                    string connectString = iniSettings.SQLConnectionString;
                    // Connection String to Excel Workbook
                    string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;Persist Security Info=False", path);

                    connection.ConnectionString = excelConnectionString;
                    OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
                    connection.Open();
                    // Create DbDataReader to Data Worksheet
                    DbDataReader dr = command.ExecuteReader();

                    // Bulk Copy to SQL Server 
                    SqlBulkCopy bulkInsert = new SqlBulkCopy(connectString);
                    bulkInsert.DestinationTableName = "[rrc_database].[dbo].[242_MachineList]";

                    SqlBulkCopyColumnMapping date = new SqlBulkCopyColumnMapping("MachineID", "MachineID");
                    bulkInsert.ColumnMappings.Add(date);

                    SqlBulkCopyColumnMapping MachineGroup1 = new SqlBulkCopyColumnMapping("MachineGroup1", "MachineGroup1");
                    bulkInsert.ColumnMappings.Add(MachineGroup1);

                    SqlBulkCopyColumnMapping MachineGroup2 = new SqlBulkCopyColumnMapping("MachineGroup2", "MachineGroup2");
                    bulkInsert.ColumnMappings.Add(MachineGroup2);

                    bulkInsert.WriteToServer(dr);

                    connection.Close();
                    MessageBox.Show("Đã Import xong");
                    cbTable_SelectedIndexChanged(sender, e);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    connection.Close();
                }

            }
        }
        #endregion

        #region 14 MistakeList
        void IMport14(object sender, EventArgs e)
        {
            FileDialog fileDialog = new OpenFileDialog();
            fileDialog.Filter = "Excel 2007-2013|*.xlsx|Excel 97-2003 (*.xls)|*.xls";
            if (fileDialog.ShowDialog() == DialogResult.OK)
            {
                OleDbConnection connection = new OleDbConnection();
                try
                {
                    string path = fileDialog.FileName;
                    INISettings iniSettings = new INISettings();
                    string connectString = iniSettings.SQLConnectionString;
                    // Connection String to Excel Workbook
                    string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;Persist Security Info=False", path);

                    connection.ConnectionString = excelConnectionString;
                    OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
                    connection.Open();
                    // Create DbDataReader to Data Worksheet
                    DbDataReader dr = command.ExecuteReader();

                    // Bulk Copy to SQL Server 
                    SqlBulkCopy bulkInsert = new SqlBulkCopy(connectString);
                    bulkInsert.DestinationTableName = "[rrc_database].[dbo].[242_MistakeList]";

                    SqlBulkCopyColumnMapping date = new SqlBulkCopyColumnMapping("MistakeID", "MistakeID");
                    bulkInsert.ColumnMappings.Add(date);

                    bulkInsert.WriteToServer(dr);

                    connection.Close();
                    MessageBox.Show("Đã Import xong");
                    cbTable_SelectedIndexChanged(sender, e);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    connection.Close();
                }

            }
        }
        #endregion

        #region 15 CauseList
        void IMport15(object sender, EventArgs e)
        {
            FileDialog fileDialog = new OpenFileDialog();
            fileDialog.Filter = "Excel 2007-2013|*.xlsx|Excel 97-2003 (*.xls)|*.xls";
            if (fileDialog.ShowDialog() == DialogResult.OK)
            {
                OleDbConnection connection = new OleDbConnection();
                try
                {
                    string path = fileDialog.FileName;
                    INISettings iniSettings = new INISettings();
                    string connectString = iniSettings.SQLConnectionString;
                    // Connection String to Excel Workbook
                    string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;Persist Security Info=False", path);

                    connection.ConnectionString = excelConnectionString;
                    OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
                    connection.Open();
                    // Create DbDataReader to Data Worksheet
                    DbDataReader dr = command.ExecuteReader();

                    // Bulk Copy to SQL Server 
                    SqlBulkCopy bulkInsert = new SqlBulkCopy(connectString);
                    bulkInsert.DestinationTableName = "[rrc_database].[dbo].[242_CauseList]";

                    SqlBulkCopyColumnMapping date = new SqlBulkCopyColumnMapping("CauseID", "CauseID");
                    bulkInsert.ColumnMappings.Add(date);

                    bulkInsert.WriteToServer(dr);

                    connection.Close();
                    MessageBox.Show("Đã Import xong");
                    cbTable_SelectedIndexChanged(sender, e);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    connection.Close();
                }

            }
        }
        #endregion

        #region 16 và 19 TimeCard và TimeCard1
        void IMport16(object sender, EventArgs e, string table)
        {
            FileDialog fileDialog = new OpenFileDialog();
            fileDialog.Filter = "Excel 2007-2013|*.xlsx|Excel 97-2003 (*.xls)|*.xls";
            if (fileDialog.ShowDialog() == DialogResult.OK)
            {
                OleDbConnection connection = new OleDbConnection();
                try
                {
                    string path = fileDialog.FileName;
                    INISettings iniSettings = new INISettings();
                    string connectString = iniSettings.SQLConnectionString;
                    // Connection String to Excel Workbook
                    string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;Persist Security Info=False", path);

                    connection.ConnectionString = excelConnectionString;
                    OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
                    connection.Open();
                    // Create DbDataReader to Data Worksheet
                    DbDataReader dr = command.ExecuteReader();

                    // Bulk Copy to SQL Server 
                    SqlBulkCopy bulkInsert = new SqlBulkCopy(connectString);
                    bulkInsert.DestinationTableName = table;//"[rrc_database].[dbo].[242_TimeCard]";

                    SqlBulkCopyColumnMapping date = new SqlBulkCopyColumnMapping("Date", "Date");
                    bulkInsert.ColumnMappings.Add(date);

                    SqlBulkCopyColumnMapping StaffID = new SqlBulkCopyColumnMapping("StaffID", "StaffID");
                    bulkInsert.ColumnMappings.Add(StaffID);

                    SqlBulkCopyColumnMapping In = new SqlBulkCopyColumnMapping("In", "In");
                    bulkInsert.ColumnMappings.Add(In);

                    SqlBulkCopyColumnMapping Out = new SqlBulkCopyColumnMapping("Out", "Out");
                    bulkInsert.ColumnMappings.Add(Out);

                    SqlBulkCopyColumnMapping Note = new SqlBulkCopyColumnMapping("Note", "Note");
                    bulkInsert.ColumnMappings.Add(Note);

                    SqlBulkCopyColumnMapping Time = new SqlBulkCopyColumnMapping("Time", "Time");
                    bulkInsert.ColumnMappings.Add(Time);

                    SqlBulkCopyColumnMapping Shift = new SqlBulkCopyColumnMapping("Shift", "Shift");
                    bulkInsert.ColumnMappings.Add(Shift);

                    SqlBulkCopyColumnMapping Overtime = new SqlBulkCopyColumnMapping("Overtime", "Overtime");
                    bulkInsert.ColumnMappings.Add(Overtime);

                    SqlBulkCopyColumnMapping ABS = new SqlBulkCopyColumnMapping("ABS", "ABS");
                    bulkInsert.ColumnMappings.Add(ABS);

                    bulkInsert.WriteToServer(dr);

                    connection.Close();
                    MessageBox.Show("Đã Import xong");
                    cbTable_SelectedIndexChanged(sender, e);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    connection.Close();
                }

            }
        }
        #endregion

        #region 17  StaffList
        void IMport17(object sender, EventArgs e, string table)
        {
            FileDialog fileDialog = new OpenFileDialog();
            fileDialog.Filter = "Excel 2007-2013|*.xlsx|Excel 97-2003 (*.xls)|*.xls";
            if (fileDialog.ShowDialog() == DialogResult.OK)
            {
                OleDbConnection connection = new OleDbConnection();
                try
                {
                    string path = fileDialog.FileName;
                    INISettings iniSettings = new INISettings();
                    string connectString = iniSettings.SQLConnectionString;
                    // Connection String to Excel Workbook
                    string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;Persist Security Info=False", path);

                    connection.ConnectionString = excelConnectionString;
                    OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
                    connection.Open();
                    // Create DbDataReader to Data Worksheet
                    DbDataReader dr = command.ExecuteReader();

                    // Bulk Copy to SQL Server 
                    SqlBulkCopy bulkInsert = new SqlBulkCopy(connectString);
                    bulkInsert.DestinationTableName = table;// "[rrc_database].[dbo].[242_CauseList]";

                    SqlBulkCopyColumnMapping StaffID = new SqlBulkCopyColumnMapping("StaffID", "StaffID");
                    bulkInsert.ColumnMappings.Add(StaffID);

                    SqlBulkCopyColumnMapping StaffName = new SqlBulkCopyColumnMapping("StaffName", "StaffName");
                    bulkInsert.ColumnMappings.Add(StaffName);

                    SqlBulkCopyColumnMapping Description = new SqlBulkCopyColumnMapping("Description", "Description");
                    bulkInsert.ColumnMappings.Add(Description);

                    SqlBulkCopyColumnMapping Group1 = new SqlBulkCopyColumnMapping("Group1", "Group1");
                    bulkInsert.ColumnMappings.Add(Group1);

                    SqlBulkCopyColumnMapping Group2 = new SqlBulkCopyColumnMapping("Group2", "Group2");
                    bulkInsert.ColumnMappings.Add(Group2);

                    SqlBulkCopyColumnMapping Dept = new SqlBulkCopyColumnMapping("Dept", "Dept");
                    bulkInsert.ColumnMappings.Add(Dept);

                    bulkInsert.WriteToServer(dr);

                    connection.Close();
                    MessageBox.Show("Đã Import xong");
                    cbTable_SelectedIndexChanged(sender, e);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    connection.Close();
                }

            }
        }
        #endregion

        #region 18  StaffList
        void IMport18(object sender, EventArgs e, string table)
        {
            FileDialog fileDialog = new OpenFileDialog();
            fileDialog.Filter = "Excel 2007-2013|*.xlsx|Excel 97-2003 (*.xls)|*.xls";
            if (fileDialog.ShowDialog() == DialogResult.OK)
            {
                OleDbConnection connection = new OleDbConnection();
                try
                {
                    string path = fileDialog.FileName;
                    INISettings iniSettings = new INISettings();
                    string connectString = iniSettings.SQLConnectionString;
                    // Connection String to Excel Workbook
                    string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;Persist Security Info=False", path);

                    connection.ConnectionString = excelConnectionString;
                    OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
                    connection.Open();
                    // Create DbDataReader to Data Worksheet
                    DbDataReader dr = command.ExecuteReader();

                    // Bulk Copy to SQL Server 
                    SqlBulkCopy bulkInsert = new SqlBulkCopy(connectString);
                    bulkInsert.DestinationTableName = table;// "[rrc_database].[dbo].[242_CauseList]";

                    SqlBulkCopyColumnMapping StaffID = new SqlBulkCopyColumnMapping("StaffID", "StaffID");
                    bulkInsert.ColumnMappings.Add(StaffID);

                    SqlBulkCopyColumnMapping StaffName = new SqlBulkCopyColumnMapping("StaffName", "StaffName");
                    bulkInsert.ColumnMappings.Add(StaffName);

                    SqlBulkCopyColumnMapping Description = new SqlBulkCopyColumnMapping("Group", "Group");
                    bulkInsert.ColumnMappings.Add(Description);

                    SqlBulkCopyColumnMapping Group1 = new SqlBulkCopyColumnMapping("GrandGroup", "GrandGroup");
                    bulkInsert.ColumnMappings.Add(Group1);

                    bulkInsert.WriteToServer(dr);

                    connection.Close();
                    MessageBox.Show("Đã Import xong");
                    cbTable_SelectedIndexChanged(sender, e);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    connection.Close();
                }

            }
        }
        #endregion

        private void btDelete_Click(object sender, EventArgs e)
        {
            if (cbTable.SelectedIndex.ToString() == "0")
            {
                clsLVDAO.DeleteTnhaporder();
                dt = clsLVDAO.Tnhaporder();
                gridControl1.DataSource = dt;
                gridView1.PopulateColumns();
                gridView1.BestFitColumns();
            }
            else if (cbTable.SelectedIndex.ToString() == "1")
            {
                clsLVDAO.DeleteXTDonhangmuangoai();
                dt = clsLVDAO.XTDonhangmuangoai();
                gridControl1.DataSource = dt;
                gridView1.PopulateColumns();
                gridView1.BestFitColumns();
            }
            else if (cbTable.SelectedIndex.ToString() == "2")
            {
                clsLVDAO.DeleteNhapOrderDonHangChuyenKho();
                dt = clsLVDAO.NhapOrderDonHangChuyenKho();
                gridControl1.DataSource = dt;
                gridView1.PopulateColumns();
                gridView1.BestFitColumns();
            }
            else if (cbTable.SelectedIndex.ToString() == "3")
            {
                clsLVDAO.DeleteHENKH();
                dt = clsLVDAO.HENKH();
                gridControl1.DataSource = dt;
                gridView1.PopulateColumns();
                gridView1.BestFitColumns();
            }
            else if (cbTable.SelectedIndex.ToString() == "4")
            {
                clsLVDAO.DeleteNhaplenhtinhthoigianGC();
                dt = clsLVDAO.GetAllGC();
                gridControl1.DataSource = dt;
                gridView1.PopulateColumns();
                gridView1.BestFitColumns();
            }
            else if (cbTable.SelectedIndex.ToString() == "5")
            {
                clsLVDAO.DeleteNhapOrderlapKHGC();
                dt = clsLVDAO.GetAllKHGC();
                gridControl1.DataSource = dt;
                gridView1.PopulateColumns();
                gridView1.BestFitColumns();
            }
            else if (cbTable.SelectedIndex.ToString() == "6")
            {
                clsLVDAO.DeleteNhapOrderlapKHNCV();
                dt = clsLVDAO.GetAllKHNCV();
                gridControl1.DataSource = dt;
                gridView1.PopulateColumns();
                gridView1.BestFitColumns();
            }
            else if (cbTable.SelectedIndex.ToString() == "7")
            {
                clsLVDAO.DeleteNhapOrderlapKHKtra();
                dt = clsLVDAO.GetAllKHKT();
                gridControl1.DataSource = dt;
                gridView1.PopulateColumns();
                gridView1.BestFitColumns();
            }
            else if (cbTable.SelectedIndex.ToString() == "8")
            {
                BaseDAO.ExecuteQuery("Delete from [242_CheckOrder]");
                dt = BaseDAO.ExecuteQuery("Select * from [242_CheckOrder]");
                gridControl1.DataSource = dt;
                gridView1.PopulateColumns();
                gridView1.BestFitColumns();
            }
            else if (cbTable.SelectedIndex.ToString() == "9")
            {
                clsLVDAO.DeleteWorkName();
                dt = clsLVDAO.WorkName();
                gridControl1.DataSource = dt;
                gridView1.PopulateColumns();
                gridView1.BestFitColumns();
            }
            else if (cbTable.SelectedIndex.ToString() == "10")
            {
                clsLVDAO.DeleteShift();
                dt = clsLVDAO.Shift();
                gridControl1.DataSource = dt;
                gridView1.PopulateColumns();
                gridView1.BestFitColumns();
            }
            else if (cbTable.SelectedIndex.ToString() == "11")
            {
                clsLVDAO.DeleteThuy0480();
                dt = clsLVDAO.Thuy0480();
                gridControl1.DataSource = dt;
                gridView1.PopulateColumns();
                gridView1.BestFitColumns();
            }
            else if (cbTable.SelectedIndex.ToString() == "12")
            {
                clsLVDAO.DeleteInternalNGDetails();
                dt = clsLVDAO.InternalNGDetails();
                gridControl1.DataSource = dt;
                gridView1.PopulateColumns();
                gridView1.BestFitColumns();
            }
            else if (cbTable.SelectedIndex.ToString() == "13")
            {
                clsLVDAO.DeleteMachineList();
                dt = clsLVDAO.MachineList();
                gridControl1.DataSource = dt;
                gridView1.PopulateColumns();
                gridView1.BestFitColumns();
            }
            else if (cbTable.SelectedIndex.ToString() == "14")
            {
                clsLVDAO.DeleteMistakeList();
                dt = clsLVDAO.MistakeList();
                gridControl1.DataSource = dt;
                gridView1.PopulateColumns();
                gridView1.BestFitColumns();
            }
            else if (cbTable.SelectedIndex.ToString() == "15")
            {
                clsLVDAO.DeleteCauseList();
                dt = clsLVDAO.CauseList();
                gridControl1.DataSource = dt;
                gridView1.PopulateColumns();
                gridView1.BestFitColumns();
            }
            else if (cbTable.SelectedIndex.ToString() == "16")
            {
                clsLVDAO.DeleteTimeCard();
                dt = clsLVDAO.TimeCard();
                gridControl1.DataSource = dt;
                gridView1.PopulateColumns();
                gridView1.BestFitColumns();
            }
            else if (cbTable.SelectedIndex.ToString() == "17")
            {
                clsLVDAO.DeleteStaffList();
                dt = clsLVDAO.StaffList();
                gridControl1.DataSource = dt;
                gridView1.PopulateColumns();
                gridView1.BestFitColumns();
            }
            else if (cbTable.SelectedIndex.ToString() == "18")
            {
                clsLVDAO.DeleteStaffList1();
                dt = clsLVDAO.StaffList1();
                gridControl1.DataSource = dt;
                gridView1.PopulateColumns();
                gridView1.BestFitColumns();
            }
            else if (cbTable.SelectedIndex.ToString() == "19")
            {
                clsLVDAO.DeleteTimeCard1();
                dt = clsLVDAO.TimeCard1();
                gridControl1.DataSource = dt;
                gridView1.PopulateColumns();
                gridView1.BestFitColumns();
            }
        }
        #endregion

        private void btnRefresh_Click(object sender, EventArgs e)
        {

        }
    }
}
